using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using eShop.Business;
using Entity;
using eShop.Model;
using eShop.Utility;


namespace eShop.DataAccess
{
    public partial class ProductDAO : DatabaseUtility
    {

        public static bool DeleteTblProduct
        (
            Int32 productId
        )
        {
            //Validate Input
            if (productId <= GetProductIdMinValue)
                throw (new ArgumentOutOfRangeException("productId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, productId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCT_DELETETBLPRODUCT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        public static bool DeleteTblProductList(System.Collections.Generic.List<Int32> productIdListToDelete)
        {
            //Validate Input
            foreach (Int32 productId in productIdListToDelete)
                if (productId <= GetProductIdMinValue)
                    throw (new ArgumentOutOfRangeException("productId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(productIdListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ProductIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCT_DELETETBLPRODUCTLIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == productIdListToDelete.Count ? true : false);
        }

        public static Product GetTblProductByProductId(Int32 productId)
        {
            if (productId <= GetProductIdMinValue)
                throw (new ArgumentOutOfRangeException("productId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, productId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCT_GETTBLPRODUCTBYPRODUCTID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static List<Product> GetAllTblProduct()
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCT_GETALLTBLPRODUCT);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        public static List<Product> GetAllTblProductPaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "ProductId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCT_GETALLTBLPRODUCTPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllTblProductPagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCT_GETALLTBLPRODUCTPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static bool Save(Product product)
        {
            if (product.ProductId <= GetProductIdMinValue)
            {
                Int32 TempId = CreateNewTblProduct(product);
                if (TempId > 0)
                {
                    product.ProductId = TempId;
                    return true;
                }
                else
                {
                    // error occurs
                    return false;
                }
            }
            else
            {	//This is a update Command
                return (UpdateTblProduct(product));
            }

        }

        public static Int32 CreateNewTblProduct(Product newTblProduct)
        {
            // validate Parameters
            if (newTblProduct == null)
                throw (new ArgumentNullException("newTblProduct"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductName);
            AddParameterToSqlCmd(sqlCmd, "@ProductName2", SqlDbType.NVarChar, 500, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@ProductDescription", SqlDbType.Text, newTblProduct.ProductDescription.Length, ParameterDirection.Input, newTblProduct.ProductDescription);
            AddParameterToSqlCmd(sqlCmd, "@ProductImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductImageUrl);
            AddParameterToSqlCmd(sqlCmd, "@DisplayOrder", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProduct.DisplayOrder);
            AddParameterToSqlCmd(sqlCmd, "@VisitCount", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ProductUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductUrl);
            AddParameterToSqlCmd(sqlCmd, "@ProductPrice", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.ProductPrice);
            AddParameterToSqlCmd(sqlCmd, "@OldPrice", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.ClearencePrice);
            AddParameterToSqlCmd(sqlCmd, "@RetailPrice", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalPrice", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalPriceDesc", SqlDbType.NVarChar, string.Empty.Length, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@Recipies", SqlDbType.NVarChar, string.Empty.Length, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalInfo", SqlDbType.NVarChar, string.Empty.Length, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@CreateDate", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProduct.ItemNumber);
            AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@IsFreeShipping", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@IsNewArrival", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@IsClearenceItem", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.BrandId);
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.CategoryId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentCost", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@StandardCost", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@IsSpecial", SqlDbType.Bit, 0, ParameterDirection.Input, false);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCT_CREATENEWTBLPRODUCT);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

        }

        public static bool UpdateTblProduct(Product newTblProduct)
        {
            // validate Parameters
            if (newTblProduct == null)
                throw (new ArgumentNullException("newTblProduct"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.ProductId);
            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductName);
            AddParameterToSqlCmd(sqlCmd, "@ProductName2", SqlDbType.NVarChar, 500, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@ProductDescription", SqlDbType.Text, newTblProduct.ProductDescription.Length, ParameterDirection.Input, newTblProduct.ProductDescription);
            AddParameterToSqlCmd(sqlCmd, "@ProductImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductImageUrl);
            AddParameterToSqlCmd(sqlCmd, "@DisplayOrder", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProduct.DisplayOrder);
            AddParameterToSqlCmd(sqlCmd, "@VisitCount", SqlDbType.Int, 0, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@ProductUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductUrl);
            AddParameterToSqlCmd(sqlCmd, "@ProductPrice", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.ProductPrice);
            AddParameterToSqlCmd(sqlCmd, "@OldPrice", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.ClearencePrice);
            AddParameterToSqlCmd(sqlCmd, "@RetailPrice", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.RetailPrice);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalPrice", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalPriceDesc", SqlDbType.NVarChar, string.Empty.Length, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@Recipies", SqlDbType.NVarChar, string.Empty.Length, ParameterDirection.Input, string.Empty);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalInfo", SqlDbType.NVarChar, newTblProduct.Specification.Length, ParameterDirection.Input, newTblProduct.Specification);
            AddParameterToSqlCmd(sqlCmd, "@CreateDate", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now);
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProduct.ItemNumber);
            AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsFeatured);
            AddParameterToSqlCmd(sqlCmd, "@IsFreeShipping", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@IsNewArrival", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsNewArrival);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, false);
            AddParameterToSqlCmd(sqlCmd, "@IsClearenceItem", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsClearenceItem);
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.BrandId);
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.CategoryId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentCost", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@StandardCost", SqlDbType.Decimal, 9, ParameterDirection.Input, 0);
            AddParameterToSqlCmd(sqlCmd, "@IsSpecial", SqlDbType.Bit, 0, ParameterDirection.Input, false);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCT_UPDATETBLPRODUCT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        public static IList GenerateTblProductCollectionFromReader(IDataReader returnData)
        {
            List<Product> colTblProduct = new List<Product>();

            while (returnData.Read())
            {
                //passing the TblProduct constructor parameters from the current instance of data reader fields.
                Product newTblProduct = new Product()
                {
                    ProductId = returnData["ProductId"] == System.DBNull.Value ? GetProductIdMinValue : (Int32)returnData["ProductId"],
                    ProductName = returnData["ProductName"] == System.DBNull.Value ? string.Empty : (String)returnData["ProductName"],
                    ProductDescription = returnData["ProductDescription"] == System.DBNull.Value ? null : (String)returnData["ProductDescription"],
                    ProductImageUrl = returnData["ProductImageUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["ProductImageUrl"],
                    DisplayOrder = returnData["DisplayOrder"] == System.DBNull.Value ? string.Empty : (String)returnData["DisplayOrder"],
                    ProductUrl = returnData["ProductUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["ProductUrl"],
                    ProductPrice = (double)(returnData["ProductPrice"] == System.DBNull.Value ? 0 : (Decimal)returnData["ProductPrice"]),
                    RetailPrice = (double)(returnData["RetailPrice"] == System.DBNull.Value ? 0 : (Decimal)returnData["RetailPrice"]),
                    Specification = returnData["AdditionalInfo"] == System.DBNull.Value ? string.Empty : (String)returnData["AdditionalInfo"],
                    ItemNumber = returnData["ItemNumber"] == System.DBNull.Value ? string.Empty : (String)returnData["ItemNumber"],
                    IsFeatured = returnData["IsFeatured"] == System.DBNull.Value ? false : (Boolean)returnData["IsFeatured"],
                    IsNewArrival = returnData["IsNewArrival"] == System.DBNull.Value ? false : (Boolean)returnData["IsNewArrival"],
                    IsActive = returnData["IsActive"] == System.DBNull.Value ? false : (Boolean)returnData["IsActive"],
                    IsClearenceItem = returnData["IsClearenceItem"] == System.DBNull.Value ? false : (Boolean)returnData["IsClearenceItem"],
                    BrandId = returnData["BrandId"] == System.DBNull.Value ? 0 : (Int32)returnData["BrandId"],
                    CategoryId = returnData["CategoryId"] == System.DBNull.Value ? 0 : (Int32)returnData["CategoryId"],
                    ClearencePrice = (double)(returnData["OldPrice"] == System.DBNull.Value ? 0 : (Decimal)returnData["OldPrice"])
                };
                //adding the Employee to the collection
                colTblProduct.Add(newTblProduct);
            }

            //returns the collection of Companys 
            return (colTblProduct);
        }

        public static Int32 GetProductIdMinValue
        {
            get
            {
                return 0;
            }
        }

        //create method related contants
        internal const string SPR_TBLPRODUCT_CREATENEWTBLPRODUCT = "spr_TblProduct_CreateNewTblProduct";
        internal const string SPR_TBLPRODUCT_UPDATETBLPRODUCT = "spr_TblProduct_UpdateTblProduct";
        internal const string SPR_TBLPRODUCT_DELETETBLPRODUCT = "spr_TblProduct_DeleteTblProduct";
        internal const string SPR_TBLPRODUCT_DELETETBLPRODUCTLIST = "spr_TblProduct_DeleteTblProductList";
        internal const string SPR_TBLPRODUCT_GETTBLPRODUCTBYPRODUCTID = "spr_TblProduct_GetTblProductByProductId";
        internal const string SPR_TBLPRODUCT_GETALLTBLPRODUCT = "spr_TblProduct_GetAllTblProduct";
        internal const string SPR_TBLPRODUCT_GETALLTBLPRODUCTPAGED = "spr_TblProduct_GetAllTblProductPaged";
        internal const string SPR_TBLPRODUCT_GETALLTBLPRODUCTPAGEDCOUNT = "spr_TblProduct_GetAllTblProductPagedCount";

        public static List<Product> GetNonDeletedProductsByBrand(int brandId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_BRAND);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetActiveProductsByBrand(int brandId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_BY_BRAND);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetActiveProductsByCategory(string orderby, int categoryId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderby);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_BY_CATEGORY);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetNonDeletedProductsByCategory(int categoryId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_CATEGORY);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetAllProductsByBrand(int brandId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ALL_PRODUCT_BY_BRAND);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetAllProductsByCategory(int categoryId, int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ALL_PRODUCT_BY_CATEGORY);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetNonDeletedProducts(int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetActiveProducts(int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetActiveSpecialProducts(int currentPageIndex, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_SPECIAL_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static int GetAllProductsCountByCategory(int categoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ALL_PRODUCT_COUNT_BY_CATEGORY);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetNonDeletedProductsCountByCategory(int categoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT_BY_CATEGORY);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetActiveProductsCountByCategory(int categoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT_BY_CATEGORY);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetAllProductsCountByBrand(int brandId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ALL_PRODUCT_COUNT_BY_BRAND);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetNonDeletedProductsCountByBrand(int brandId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT_BY_BRAND);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetActiveProductsCountByBrand(int brandId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@BrandId", SqlDbType.Int, 0, ParameterDirection.Input, brandId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT_BY_BRAND);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetActiveProductsCount()
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetNonDeletedProductsCount()
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int CountItemNumber(string itemNumber)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 50, ParameterDirection.Input, itemNumber);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_ITEM_NUMBER_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetBackEndProductListCount(string productName, int categoryId, bool includeDeleted)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();
            if (categoryId > 0)
            {
                condition.Append(" Where CategoryId = ").Append(categoryId.ToString()).Append(" ");
            }
            if (!string.IsNullOrEmpty(productName))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
                else
                    condition.Append(" WHERE ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
            }
            if (!includeDeleted)
            {
                if (condition.Length > 0)
                    condition.Append(" AND IsDeleted = 0 ");
                else
                    condition.Append(" WHERE IsDeleted = 0 ");
            }

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@condition", SqlDbType.NVarChar, 500, ParameterDirection.Input, condition.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST_COUNT);

            int returnValue = Convert.ToInt32(ExecuteScalarCmd(sqlCmd));
            return returnValue;
        }

        public static int GetBackEndProductListCountAdmin(string productName, string itemNumber, int categoryId, bool includeDeleted)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();
            if (categoryId > 0)
            {
                condition.Append(" Where CategoryId = ").Append(categoryId.ToString()).Append(" ");
            }
            if (!string.IsNullOrEmpty(productName))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
                else
                    condition.Append(" WHERE ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
            }
            if (!string.IsNullOrEmpty(itemNumber))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ItemNumber LIKE '%").Append(string.IsNullOrEmpty(itemNumber) ? "%" : itemNumber).Append("%' ");
                else
                    condition.Append(" WHERE ItemNumber LIKE '%").Append(string.IsNullOrEmpty(itemNumber) ? "%" : itemNumber).Append("%' ");
            }
            if (!includeDeleted)
            {
                if (condition.Length > 0)
                    condition.Append(" AND IsDeleted = 0 ");
                else
                    condition.Append(" WHERE IsDeleted = 0 ");
            }

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@condition", SqlDbType.NVarChar, 500, ParameterDirection.Input, condition.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST_COUNT);

            int returnValue = Convert.ToInt32(ExecuteScalarCmd(sqlCmd));
            return returnValue;
        }

        public static List<Product> GetBackEndProductList(string productName, int categoryId, bool includeDeleted, string sortBy, int firstItem, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();
            if (categoryId > 0)
            {
                condition.Append(" Where CategoryId = ").Append(categoryId.ToString()).Append(" ");
            }
            if (!string.IsNullOrEmpty(productName))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
                else
                    condition.Append(" WHERE ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
            }
            if (!includeDeleted)
            {
                if (condition.Length > 0)
                    condition.Append(" AND IsDeleted = 0 ");
                else
                    condition.Append(" WHERE IsDeleted = 0 ");
            }

            AddParameterToSqlCmd(sqlCmd, "@Condition", SqlDbType.NVarChar, 500, ParameterDirection.Input, condition.ToString());
            AddParameterToSqlCmd(sqlCmd, "@Orderby", SqlDbType.NVarChar, 500, ParameterDirection.Input, sortBy);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, firstItem);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetBackEndProductListAdmin(string productName, string itemNumber, int categoryId, bool includeDeleted, string sortBy, int firstItem, int pageSize)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            StringBuilder condition = new StringBuilder();
            if (categoryId > 0)
            {
                condition.Append(" Where CategoryId = ").Append(categoryId.ToString()).Append(" ");
            }
            if (!string.IsNullOrEmpty(productName))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
                else
                    condition.Append(" WHERE ProductName LIKE '%").Append(string.IsNullOrEmpty(productName) ? "%" : productName).Append("%' ");
            }
            if (!string.IsNullOrEmpty(itemNumber))
            {
                if (condition.Length > 0)
                    condition.Append(" AND ItemNumber LIKE '%").Append(string.IsNullOrEmpty(itemNumber) ? "%" : itemNumber).Append("%' ");
                else
                    condition.Append(" WHERE ItemNumber LIKE '%").Append(string.IsNullOrEmpty(itemNumber) ? "%" : itemNumber).Append("%' ");
            }
            if (!includeDeleted)
            {
                if (condition.Length > 0)
                    condition.Append(" AND IsDeleted = 0 ");
                else
                    condition.Append(" WHERE IsDeleted = 0 ");
            }

            AddParameterToSqlCmd(sqlCmd, "@Condition", SqlDbType.NVarChar, 500, ParameterDirection.Input, condition.ToString());
            AddParameterToSqlCmd(sqlCmd, "@Orderby", SqlDbType.NVarChar, 500, ParameterDirection.Input, sortBy);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, firstItem);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetFeaturedProducts(int howMany)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_FEATURED_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetAllFeaturedProducts(int howMany)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NONDELETED_FEATURED_PRODUCT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetProductByKeywordAndItemNumber(string keyword, string itemNumber)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, 500, ParameterDirection.Input, keyword);
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 500, ParameterDirection.Input, itemNumber);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_BY_KEYWORD_AND_ITEMNUMBER);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }
        public static List<Product> GetProductByBothName(int currentPageIndex, int pageSize, string keyword, string itemNumber)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, 500, ParameterDirection.Input, keyword);
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 500, ParameterDirection.Input, itemNumber);
            AddParameterToSqlCmd(sqlCmd, "@CurrentPageIndex", SqlDbType.Int, 0, ParameterDirection.Input, currentPageIndex);
            AddParameterToSqlCmd(sqlCmd, "@PageSize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, "sp_TblProduct_Get_Product_By_Both_Name");
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }
        public static int GetProductByBothNameCount(string keyword, string itemNumber)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductName", SqlDbType.NVarChar, 500, ParameterDirection.Input, keyword);
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 500, ParameterDirection.Input, itemNumber);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "sp_TblProduct_Get_Product_By_Both_Name_Count");
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static List<Product> GetProductsByItemNumberPrefix(string prefix, int howMany)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@Prefix", SqlDbType.NVarChar, 500, ParameterDirection.Input, prefix);
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_PRODUCT_BY_ITEMNUMBER_PREFIX);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static Product GetProductByItemNumber(string itemNumber)
        {
            if (string.IsNullOrEmpty(itemNumber))
                itemNumber = "%";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 500, ParameterDirection.Input, itemNumber);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCT_GET_PRODUCT_BY_ITEM_NUMBER);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static List<Product> GetNonDeletedProductsByItemNumberPrefix(string prefix, int howMany)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@Prefix", SqlDbType.NVarChar, 500, ParameterDirection.Input, prefix);
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_ITEMNUMBER_PREFIX);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static Product GetNonDeletedProductByItemNumber(string itemNumber)
        {
            if (string.IsNullOrEmpty(itemNumber))
                itemNumber = "%";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ItemNumber", SqlDbType.NVarChar, 500, ParameterDirection.Input, itemNumber);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCT_GET_NON_DELETED_PRODUCT_BY_ITEM_NUMBER);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static List<Product> GetNonDeletedProductListForFavorites()
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_FOR_FAVORITES);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetBackendProductPurchaseProductListBySoldDate(DateTime fromdate, DateTime todate)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@fromdate", SqlDbType.DateTime, 0, ParameterDirection.Input, fromdate);
            AddParameterToSqlCmd(sqlCmd, "@todate", SqlDbType.DateTime, 0, ParameterDirection.Input, todate);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCT_PURCHASE_GET_PRODUCTS);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static List<Product> GetProductByCategoryURL(string categoryURL)
        {
            if (categoryURL.EndsWith("/"))
                categoryURL = "~/" + categoryURL;
            else
                categoryURL = "~/" + categoryURL + "/";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryUrl", SqlDbType.NVarChar, categoryURL.Length, ParameterDirection.Input, categoryURL);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProduct_GetTblProductByCategoryURL");
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection;
            else
                return null;
        }

        private const string SP_TBL_PRODUCT_PURCHASE_GET_PRODUCTS = "sp_get_all_products_by_sale_date";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_SPECIAL_PRODUCT = "sp_TblProduct_Get_Active_Special_Product";

        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_BRAND = "sp_TblProduct_Get_Non_Deleted_Product_By_Brand";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_BY_BRAND = "sp_TblProduct_Get_Active_Product_By_Brand";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_BY_CATEGORY = "sp_TblProduct_Get_Active_Product_By_Category";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_CATEGORY = "sp_TblProduct_Get_Non_Deleted_Product_By_Category";
        private const string SP_TBL_PRODUCT_GET_ALL_PRODUCT_BY_BRAND = "sp_TblProduct_Get_All_Product_By_Brand";
        private const string SP_TBL_PRODUCT_GET_ALL_PRODUCT_BY_CATEGORY = "sp_TblProduct_Get_All_Product_By_Category";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT = "sp_TblProduct_Get_Non_Deleted_Product";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT = "sp_TblProduct_Get_Active_Product";
        private const string SP_TBL_PRODUCT_GET_ALL_PRODUCT_COUNT_BY_CATEGORY = "sp_TblProduct_Get_All_Product_Count_By_Category";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT_BY_CATEGORY = "sp_TblProduct_Get_Non_Deleted_Product_Count_By_Category";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT_BY_CATEGORY = "sp_TblProduct_Get_Active_Product_Count_By_Category";
        private const string SP_TBL_PRODUCT_GET_ALL_PRODUCT_COUNT_BY_BRAND = "sp_TblProduct_Get_All_Product_Count_By_Brand";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT_BY_BRAND = "sp_TblProduct_Get_Non_Deleted_Product_Count_By_Brand";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT_BY_BRAND = "sp_TblProduct_Get_Active_Product_Count_By_Brand";
        private const string SP_TBL_PRODUCT_GET_ACTIVE_PRODUCT_COUNT = "sp_TblProduct_Get_Active_Product_Count";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_COUNT = "sp_TblProduct_Get_Non_Deleted_Product_Count";
        private const string SP_TBL_PRODUCT_GET_ITEM_NUMBER_COUNT = "sp_TblProduct_Get_ItemNumber_Count";
        private const string SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST_COUNT = "sp_TblProduct_Get_Backend_Product_List_Count";
        private const string SP_TBL_PRODUCT_GET_BACKEND_PRODUCT_LIST = "sp_TblProduct_Get_Backend_Product_List";
        private const string SP_TBL_PRODUCT_GET_FEATURED_PRODUCT = "sp_TblProduct_Get_Featured_Product";
        private const string SP_TBL_PRODUCT_GET_NONDELETED_FEATURED_PRODUCT = "sp_TblProduct_Get_NonDeletedFeatured_Product";
        private const string SP_TBL_PRODUCT_GET_PRODUCT_BY_KEYWORD_AND_ITEMNUMBER = "sp_TblProduct_Get_Product_By_Keyword_And_ItemNumber";
        private const string SP_TBLPRODUCT_GET_PRODUCT_BY_ITEM_NUMBER = "sp_TblProduct_Get_Product_By_Item_Number";
        private const string SP_TBL_PRODUCT_GET_PRODUCT_BY_ITEMNUMBER_PREFIX = "sp_TblProduct_Get_Product_By_ItemNumber_Prefix";
        private const string SP_TBLPRODUCT_GET_NON_DELETED_PRODUCT_BY_ITEM_NUMBER = "sp_TblProduct_Get_NonDeleted_Product_By_Item_Number";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_BY_ITEMNUMBER_PREFIX = "sp_TblProduct_Get_NonDeleted_Product_By_ItemNumber_Prefix";
        private const string SP_TBL_PRODUCT_GET_NON_DELETED_PRODUCT_FOR_FAVORITES = "sp_TblProduct_Get_NonDeleted_Product_For_Favorites";

        public static Product GetTblProductByProductPath(String path)
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@Productpath", SqlDbType.VarChar, path.Length, ParameterDirection.Input, path);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProduct_GetTblProductByProductPath");
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        // List of know sp removed
        // spr_TblProduct_GetSupplierTblProductByProductPath
    }
}
